package com.mayisoftware.mayioa.business.input.impl.task;


import com.mayisoftware.mayioa.business.input.api.domain.InputConstants;
import com.mayisoftware.mayioa.business.input.api.domain.UploadRecord;
import com.mayisoftware.mayioa.business.input.api.service.IUploadRecordService;
import com.mayisoftware.mayioa.business.input.impl.controller.InputUploadController;
import com.mayisoftware.mayioa.business.personal.api.domain.PersonalSign;
import com.mayisoftware.mayioa.business.personal.api.service.IPersonalSignService;
import com.mayisoftware.mayioa.business.plan.api.domain.PlanAchievement;
import com.mayisoftware.mayioa.business.plan.api.domain.PlanConfig;
import com.mayisoftware.mayioa.business.plan.api.domain.PlanDetailConfig;
import com.mayisoftware.mayioa.business.plan.api.service.IPlanConfigDetailService;
import com.mayisoftware.mayioa.business.plan.api.service.IPlanConfigService;
import com.mayisoftware.mayioa.business.pool.api.domain.PoolMgrInfo;
import com.mayisoftware.mayioa.business.pool.api.service.IPoolManualService;
import com.mayisoftware.mayioa.business.score.api.domain.ScoreInfo;
import com.mayisoftware.mayioa.business.score.api.service.IScoreInfoService;
import com.mayisoftware.mayioa.common.impl.utils.CommonCheckUtil;
import com.mayisoftware.mayioa.common.impl.utils.POIUtil;
import com.mayisoftware.mayioa.common.impl.utils.StringUtils;
import com.mayisoftware.mayioa.platform.api.domain.SysDept;
import com.mayisoftware.mayioa.platform.api.domain.SysDictData;
import com.mayisoftware.mayioa.platform.api.domain.SysPost;
import com.mayisoftware.mayioa.platform.api.domain.SysUser;
import com.mayisoftware.mayioa.platform.api.service.*;
import com.mayisoftware.mayioa.platform.impl.shiro.service.SysPasswordService;
import com.mayisoftware.mayioa.platform.impl.util.ShiroUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.tomcat.jni.Time;
import org.quartz.DisallowConcurrentExecution;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 定时任务调度计划解析
 *
 *
 */
@Component("planParseTask")
public class PlanParseTask
{
    /**
     * 日志对象
     */
    private static final Logger log = LoggerFactory.getLogger(PlanParseTask.class);

    /**
     * 系统配置服务
     */
    @Autowired
    private  ISysConfigService sysConfigService;
    /**
     * 密码服务
     */
    @Autowired
    private SysPasswordService passwordService;
    /**
     * 骨干池管理服务
     */
    @Autowired
    private IPoolManualService poolManualService;

    /**
     * 报名信息服务
     */
    @Autowired
    private IPersonalSignService personSignService;

    /**
     * 打分服务信息
     */
    @Autowired
    private IScoreInfoService scoreInfoService;

    /**
     * 计划配置服务
     */
    @Autowired
    private IPlanConfigService planConfigService;

    /**
     * 计划详情服务
     */
    @Autowired
    private IPlanConfigDetailService planConfigDetailService;


    /**
     * 职位查询服务
     */
    @Autowired
    private ISysPostService sysPostService;

    /**
     * 部门查询服务
     */
    @Autowired
    private ISysDeptService deptService;

    /**
     * 数据字典查询服务
     */
    @Autowired
    private ISysDictDataService sysDictDataService;

    /**
     * 上传记录操作服务
     */
    @Autowired
    private IUploadRecordService uploadRecordService;

    /**
     * 用户管理服务
     */
    @Autowired
    private ISysUserService sysUserService;

    public void parsePlan()
    {
        log.info("开始执行计划解析");

        try {
            //查询数据库记录
            List<UploadRecord> uploadRecords = uploadRecordService.queryRecordListNeedParse();

            if(CollectionUtils.isEmpty(uploadRecords)){
                log.warn("没有需要解析的记录");
                return;
            }

            List<SysDictData> achDatas = sysDictDataService.selectDictDataByType("plan_achievement_score");
            Map<String,BigDecimal> ach_values = new HashMap<>();

            for (SysDictData data:achDatas){
                ach_values.put(data.getDictLabel(),new BigDecimal(data.getDictValue()));
            }

            String errorFilePath = "";
            for (UploadRecord record:uploadRecords){
                boolean isOk = true;
                UploadRecord updateRecord = new UploadRecord();
                try {
                    log.info("处理记录 id: {} 文件:{}",record.getRecordId(),record.getFilePath());
                    String planFilePath = record.getFilePath();

                    File file =new File(planFilePath);
                    if(StringUtils.isEmpty(planFilePath) || !file.exists() || !file.isFile()){
                        log.error("处理记录 id: {} 文件:{}不存在",record.getRecordId(),record.getFilePath());
                        continue;
                    }

                    String fileName= file.getName();
                    String fileDir=file.getParentFile().getAbsolutePath();
                    String fileNameNoExt = fileName.substring(0,fileName.lastIndexOf("."));
                    errorFilePath = fileDir+File.separator+fileNameNoExt+".log";

                    updateRecord.setRecordId(record.getRecordId());
                    //更新DB状态 正在解析
                    updateRecord.setParseStatus("2");

                    uploadRecordService.editInputRecord(updateRecord);

                    StringBuffer planBuffer =new StringBuffer();
                    isOk = dealOneRecord(record,file,errorFilePath,ach_values,planBuffer );
//                    updateRecord.setPlanName(planBuffer.toString());
                }
                catch (Exception e){
                    log.error("处理记录 id: {} 文件:{},异常",record.getRecordId(),record.getFilePath(),e);
                }
                finally {
                    try {
                        if (isOk){
                            log.info("处理记录 id: {} 文件:{},处理成功",record.getRecordId(),record.getFilePath());
                            //更新DB状态 正在解析

                            updateRecord.setParseStatus("3");
                            uploadRecordService.editInputRecord(updateRecord);
                        }
                        else {
                            log.info("处理记录 id: {} 文件:{},处理失败",record.getRecordId(),record.getFilePath());
                            //更新DB状态 正在解析
                            updateRecord.setParseStatus("4");
                            updateRecord.setErrorPath(errorFilePath);
                            uploadRecordService.editInputRecord(updateRecord);
                        }
                    }
                    catch (Exception e){
                        log.info("处理记录状态异常",e);
                    }
                }
            }
        } catch (Exception e) {
            log.error("执行计划解析异常",e);
        }

        log.info("结束执行计划解析");
        poolManualService.modifyPoolInfoToCacl();
    }

    /**
     * 处理一个上传记录文件
     */
    private boolean dealOneRecord(UploadRecord record, File file,String  errorFilePath, Map<String,BigDecimal> ach_values ,StringBuffer planBuffer ) {

        //解析Excel，校验看是否有错误
        FileInputStream inputStream = null;
        Workbook workbook = null;
        FileWriter fileWriter = null;
        BufferedWriter bufferedWriter = null;
        try {
            if (new File(errorFilePath).exists()){
                FileUtils.deleteQuietly(new File(errorFilePath));
                log.warn("删除历史错误信息:"+errorFilePath);
            }
             inputStream = new FileInputStream(file);
             fileWriter = new FileWriter(new File(errorFilePath));
             bufferedWriter = new BufferedWriter(fileWriter);
            //读取Excel
            //检查文件
            POIUtil.checkFile(file);
            //获得Workbook工作薄对象
            workbook = POIUtil.getWorkBook(file,inputStream);

            if(workbook == null) {
                bufferedWriter.write("Excel工作对象不能为空\n");
                return false;
            }

            int sheetNum = workbook.getNumberOfSheets();

            if (sheetNum < 1){
                bufferedWriter.write("Excel工作表不能为空\n");
                return  false;
            }

            Sheet sheet = workbook.getSheetAt(0);
            if(sheet == null){
                bufferedWriter.write("Excel工作表不能为空");
                bufferedWriter.newLine();
                return  false;
            }

            String planName  = sheet.getSheetName();
            //检查计划
            PlanConfig planConfig = planConfigService.selectPlanConfigByName(planName);

            if (planConfig ==null || !planConfig.getPlanCfgStatus().equalsIgnoreCase("1")){
                bufferedWriter.write("计划不存在，请确定是否是下载的模板,或者计划不是发布状态。\n");
                return false;
            }

            //设置计划名称
            planBuffer.append(planName);

            List<PlanDetailConfig>  planDetailConfigs = planConfigDetailService.selectPlanItemByPlanId(planConfig.getPlanCfgId().longValue());

            if (CollectionUtils.isEmpty(planDetailConfigs)){
                bufferedWriter.write("计划没有配置任何考核项目。\n");
                return  false;
            }

            //重构计划项目为Map
            Map<String ,PlanDetailConfig> planDetailName_Config = new HashMap<>();

            for (PlanDetailConfig planDetailConfig:planDetailConfigs ) {
                planDetailName_Config.put(planDetailConfig.getPlanCfgItemType().toString()+"_"+planDetailConfig.getPlanCfgItemName(),planDetailConfig);
            }

            //获得当前sheet的开始行
            int firstRowNum  = sheet.getFirstRowNum();

            boolean hasError = false;
            //获得当前sheet的结束行
            int lastRowNum = sheet.getLastRowNum();
            boolean isFirst = true;
            String sheetName=  sheet.getSheetName();
            Map<String,Integer> col_pos = new HashMap<>();
            //循环
            for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){

                //获得当前行
                Row row = sheet.getRow(rowNum);
                if(row == null){
                    log.info("{}是空行",rowNum);
                    continue;
                }
                //获得当前行的开始列
                int firstCellNum = row.getFirstCellNum();
                //获得当前行的列数
                int lastCellNum = row.getPhysicalNumberOfCells();

                //循环当前行
                Map<Integer,String> cells = new HashMap<>();
                for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){

                    Cell cell = row.getCell(cellNum);
                    String value = POIUtil.getCellValue(cell);
                    cells.put(cellNum,value);
                    if (isFirst) {
                        col_pos.put(value,cellNum);
                    }
                }

                boolean ret =analysisCells( record,cells,isFirst,rowNum,sheetName,col_pos,bufferedWriter,planConfig,planDetailName_Config,ach_values );

                if (!ret){
                    hasError = true;
                }
                isFirst=false;
            }

            workbook.close();

            if (hasError){
                return false;
            }
        } catch (Exception e) {
            try {
                bufferedWriter.write("处理发生异常"+e.getMessage());
                bufferedWriter.newLine();
            } catch (IOException e1) {
                log.error("处理记录异常",e1);
            }
            log.error("处理记录异常",e);
            return  false;
        }
        finally {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(inputStream);
            IOUtils.closeQuietly(bufferedWriter);
            IOUtils.closeQuietly(fileWriter);
        }


        return true;
    }


    /**
     * 检查字典中是否含有该类型
     */
    private String checkDictData(String dictType,String cellValue){
        List<SysDictData> sysDictDataList = sysDictDataService.selectDictDataByType(dictType);

        if (!StringUtils.isEmpty(cellValue)) {
            for (SysDictData data : sysDictDataList) {
                if (data.getDictLabel().equalsIgnoreCase(cellValue)) {
                    return data.getDictValue();
                }
            }

            return  "";
        }
        return InputConstants.NULL_STR_NOT_CHECK;
    }


    /**
     * 分析每一行单元格
     */
    private boolean analysisCells(UploadRecord record,Map<Integer,String>  cells, boolean isFirst,int rowNum,String sheetName,
                               Map<String,Integer> col_pos,BufferedWriter bufferedWriter ,PlanConfig planConfig,
                               Map<String ,PlanDetailConfig> planDetailName_Config, Map<String,BigDecimal> ach_values ) throws IOException {

        if (isFirst){
           return true;
        }
        boolean hasError = false;
        boolean isUserExists = false;
        Long curUserId = -1L;
        SysUser sheetUser = new SysUser();
        SysUser user = null;
        Integer sheetUserIdPos = col_pos.get("工号");
        if (sheetUserIdPos == null){
            bufferedWriter.write(rowNum+"行没有配置工号,或者数据无效");
            bufferedWriter.newLine();
            hasError = true;
        }
        else {
            String userId = cells.get(sheetUserIdPos);

            if (StringUtils.isEmpty(userId)) {
                bufferedWriter.write(rowNum + "行没有配置工号或者数据无效");
                bufferedWriter.newLine();
                hasError = true;
            }
            else {
                //检查用户是否存在
                try {
                    curUserId=Long.parseLong(userId);
                    sheetUser.setUserId(curUserId);
                    user = sysUserService.selectUserById(curUserId);

                    if (user == null) {
//                        bufferedWriter.write(rowNum + "行工号无效");
//                        bufferedWriter.newLine();
//                        hasError = true;
                        isUserExists =false;
                    }else {
                        isUserExists =true;
                    }
                }
                //工号必须是数字
                catch (Exception e) {
                    bufferedWriter.write(rowNum + "行没有配置工号或者数据无效");
                    bufferedWriter.newLine();
                    hasError = true;
                }
            }
        }

        String userName = getValueByKey("姓名",cells, col_pos);
        if (!StringUtils.isEmpty(userName)){
            sheetUser.setUserName(userName);
        }

        String userEmail = getValueByKey("邮箱",cells, col_pos);
        if (!StringUtils.isEmpty(userEmail)){
            if (CommonCheckUtil.emailFormat(userEmail)){
                sheetUser.setEmail(userEmail);
            }
            else {
                bufferedWriter.write(rowNum+"行邮箱格式不正确.");
                bufferedWriter.newLine();
                hasError = true;
            }
        }

        String userTelephone = getValueByKey("电话",cells, col_pos);
        if (!StringUtils.isEmpty(userTelephone)){
            if(CommonCheckUtil.isNumeric(userTelephone)){
                sheetUser.setPhonenumber(userTelephone);
            }
            else {
                bufferedWriter.write(rowNum+"行电话格式不正确.");
                bufferedWriter.newLine();
                hasError = true;
            }
        }

        String userProjectRole  = getValueByKey("项目组角色",cells, col_pos);
        String userProjectRoleValue = checkDictData("sys_user_project_role",userProjectRole);
        if (userProjectRoleValue.isEmpty()){
            bufferedWriter.write(rowNum+"行项目组角色错误或未在数据字典中配置.");
            bufferedWriter.newLine();
            hasError = true;
        }
        else if (!userProjectRoleValue.equalsIgnoreCase(InputConstants.NULL_STR_NOT_CHECK)){
            sheetUser.setProjectRole(userProjectRoleValue);
        }

        String userArea  = getValueByKey("地区",cells, col_pos);
        String userAreaValue = checkDictData("sys_user_region",userArea);
        if (userAreaValue.isEmpty()){
            bufferedWriter.write(rowNum+"行地区错误或未在数据字典中配置.");
            bufferedWriter.newLine();
            hasError = true;
        }
        else if (!userAreaValue.equalsIgnoreCase(InputConstants.NULL_STR_NOT_CHECK)){
            sheetUser.setRegion(userAreaValue);
        }

        String userParent = getValueByKey("直接上级",cells, col_pos);
        if (!StringUtils.isEmpty(userParent)){
            try{
                sheetUser.setDirectSuperiorUserId(Long.parseLong(userParent));
            }
            catch (Exception e){
                log.error("直接上级格式不正确",e);
                bufferedWriter.write(rowNum+"行部直接上级格式不对.");
                bufferedWriter.newLine();
                hasError = true;
            }
        }

        String deptName = getValueByKey("成本中心",cells, col_pos);

        if(!StringUtils.isEmpty(deptName)){
            List<SysDept> sysDepts = deptService.selectDeptList(new SysDept());

            boolean bhave=false;
            for (SysDept dept:sysDepts){
                if (dept.getDeptName() != null && deptName !=null && dept.getDeptName().equalsIgnoreCase(deptName)){
                    bhave = true;
                    sheetUser.setDeptId(dept.getDeptId());
                    break;
                }
            }

            if (!bhave){
                bufferedWriter.write(rowNum+"行部门名称没有在系统中配置.");
                bufferedWriter.newLine();
                hasError = true;
            }
        }

        String sex  = getValueByKey("性别",cells, col_pos);
        String sexValue = checkDictData("sys_user_sex",sex);
        if (sexValue.isEmpty()){
            bufferedWriter.write(rowNum+"行性别配置错误.");
            bufferedWriter.newLine();
            hasError = true;
        }
        else if (!sexValue.equalsIgnoreCase(InputConstants.NULL_STR_NOT_CHECK)){
            sheetUser.setSex(sexValue);
        }

        String hwLevel  = getValueByKey("华为职级",cells, col_pos);
        String hwLevelValue = checkDictData("sys_hw_level",hwLevel);
        if(hwLevelValue.isEmpty()){
            bufferedWriter.write(rowNum+"行华为级别配置错误，或者该级别未在系统中配置.");
            bufferedWriter.newLine();
            hasError = true;
        }if (!hwLevelValue.equalsIgnoreCase(InputConstants.NULL_STR_NOT_CHECK)){
            sheetUser.setHwLevel(hwLevelValue);
        }

        String post  = getValueByKey("蚂蚁职位",cells, col_pos);

        List<Long> postIds = new ArrayList<>();
        if (!StringUtils.isEmpty(post)){
            List<SysPost> sysPosts = sysPostService.selectPostAll();
            boolean have =false;
            for (SysPost sysPost:sysPosts){
                if (sysPost.getPostName().equalsIgnoreCase(post)){
                    postIds.add(sysPost.getPostId());
                    have =true;
                }
            }

            if (!have){
                bufferedWriter.write(rowNum+"行蚂蚁职位配置错误，或者该级别未在系统中配置.");
                bufferedWriter.newLine();
                hasError = true;
            }
            else {
                sheetUser.setPostIds(postIds.toArray(new Long[postIds.size()]));
            }
        }

        String coopMode  = getValueByKey("合作模式",cells, col_pos);
        String coopModeValue = checkDictData("sys_coop_model",coopMode);
        if(coopModeValue.isEmpty()){
            bufferedWriter.write(rowNum+"行合作模式配置错误，或者该合作模式未在系统中配置.");
            bufferedWriter.newLine();
            hasError = true;
        }else if (!coopModeValue.equalsIgnoreCase(InputConstants.NULL_STR_NOT_CHECK)){
            sheetUser.setCoopModel(coopModeValue);
        }

        String joinTime  = getValueByKey("入职日期",cells, col_pos);
        if (!StringUtils.isEmpty(joinTime)){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            try {
                Date dt =sdf.parse(joinTime);
                sheetUser.setJoinTime(dt);
            }
            catch (Exception e)
            {
                bufferedWriter.write(rowNum+"行入职日期配置格式错误.");
                bufferedWriter.newLine();
                hasError = true;
            }
        }

        String workTime  = getValueByKey("工作日期",cells, col_pos);
        if (!StringUtils.isEmpty(workTime)){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            try {
                Date dt= sdf.parse(workTime);
                sheetUser.setWorkTime(dt);
            }
            catch (Exception e)
            {
                bufferedWriter.write(rowNum+"行工作日期配置格式错误.");
                bufferedWriter.newLine();
                hasError = true;
            }
        }

        String universe  = getValueByKey("毕业院校",cells, col_pos);

        if (!StringUtils.isEmpty(universe)){
            sheetUser.setSchool(universe);
        }

        String education  = getValueByKey("学历",cells, col_pos);

        if (!StringUtils.isEmpty(education)){
            sheetUser.setEducation(education);
        }

        String is211  = getValueByKey("是否211",cells, col_pos);
        String is211Value = checkDictData("sys_school_211",is211);
        if(is211Value.isEmpty()){
            bufferedWriter.write(rowNum+"行是否211配置错误，或者未在系统中配置.");
            bufferedWriter.newLine();
            hasError = true;
        }
        else if (!is211Value.equalsIgnoreCase(InputConstants.NULL_STR_NOT_CHECK)){
            sheetUser.setIs211(is211Value);
        }

        List<ScoreInfo> scoreInfos = new ArrayList<>();
        for (Map.Entry<String,Integer> colposEntry: col_pos.entrySet()){
            String colName = colposEntry.getKey();
            Integer colPos = colposEntry.getValue();
            //检查绩效是否配置

            String achName = "";
            String achValue = "";
            String type = "";
            if (colName.startsWith("绩效\n") )
            {
                type = "3";
            }

            if (colName.startsWith("课程\n") )
            {
                type = "1";
            }

            if (colName.startsWith("自定义\n") )
            {
                type = "2";
            }

            if (!type.isEmpty()){
                String [] achValues = colName.split("\n");
                if (achValues.length != 3){
                    bufferedWriter.write(rowNum+"行"+colName+"格式配置错误.");
                    bufferedWriter.newLine();
                    hasError = true;
                    continue;
                }

                //绩效名称
                 achName = achValues[1];
                //绩效字典类型为3
                if (!planDetailName_Config.containsKey(type+"_"+achName)){
                    bufferedWriter.write(rowNum+"行"+colName+" 计划没有配置该考核项目.");
                    bufferedWriter.newLine();
                    hasError = true;
                    continue;
                }

                achValue  = getValueByPos(colPos, cells);
                BigDecimal numAchValue =null;

                if (achValue != null && !achValue.isEmpty()){
                    try {
                        if (type.equalsIgnoreCase("3")){
                            BigDecimal decVal= ach_values.get(achValue);
                            if (decVal == null){
                                throw  new Exception("绩效值无效");
                            }
                            numAchValue = decVal;
                        }
                        else {
                            numAchValue= new BigDecimal(achValue);
                        }
                    }
                    catch (Exception e){
                        bufferedWriter.write(rowNum+"行"+colName+" 考核项目值不是数字或者无效.");
                        bufferedWriter.newLine();
                        hasError = true;
                        continue;
                    }
                    //根据名称读取绩效的配置信息
                    PlanDetailConfig planAchievementCfg = planDetailName_Config.get(type+"_"+achName);
                    ScoreInfo scoreInfo = new ScoreInfo();
                    scoreInfo.setScoreUserId(curUserId.intValue());
                    scoreInfo.setScoreUpdateTime(new Date());
                    scoreInfo.setScoreUpdateUserId(record.getRecordUserId());
                    scoreInfo.setScoreInfo(numAchValue);
                    scoreInfo.setScorePlanId(planConfig.getPlanCfgId());
                    scoreInfo.setScorePlanItemId(planAchievementCfg.getPlanCfgItemRecordid());
                    scoreInfo.setExists(false);
                    if (isUserExists ){
                         List<ScoreInfo> infos =  scoreInfoService.selectScoreInfoListByUserAndPlan(scoreInfo);
                         if (CollectionUtils.isEmpty(infos)){
                             scoreInfo.setExists(false);
                         }
                         else {
                             scoreInfo.setExists(true);
                             scoreInfo.setScoreRecordId(infos.get(0).getScoreRecordId());
                         }
                    }
                    scoreInfos.add(scoreInfo);
                }
            }
        }

        if (hasError || CollectionUtils.isEmpty(scoreInfos)){
            return false;
        }

        //刷新用户信息
        //刷新打分信息
        updateUserAndScoreInfo(planConfig,isUserExists,sheetUser,scoreInfos);

        return  true;

    }

    /**
     *刷新用户状态和用户打分信息
     */
    private void updateUserAndScoreInfo(PlanConfig planConfig,boolean isUserExists, SysUser sheetUser, List<ScoreInfo> scoreInfos) {
        if (isUserExists){
            sysUserService.updateUserInfo(sheetUser);
        }
        else {
            SysUser tmpUser= sysUserService.selectUserById(sheetUser.getUserId());
            if (tmpUser == null){
                sheetUser.setLoginName(sheetUser.getUserName()+sheetUser.getUserId());
                Long [] roles = new Long[1];
                roles[0] = 2L;
                sheetUser.setRoleIds(roles);
                sheetUser.setSalt(ShiroUtils.randomSalt());

                String defaultPass = sysConfigService.selectConfigByKey("sys.user.initPassword");
                sheetUser.setPassword(defaultPass);
                sheetUser.setPassword(passwordService.encryptPassword(sheetUser.getLoginName(), defaultPass, sheetUser.getSalt()));
                sysUserService.insertUser(sheetUser);
            }
            else {
                sysUserService.updateUserInfo(sheetUser);
            }
        }

        for (ScoreInfo scoreInfo:scoreInfos){
            if(scoreInfo.getExists()){
                scoreInfoService.updateByPrimaryKeySelective(scoreInfo);
            }
            else {
                scoreInfoService.insertOneScoInfo(scoreInfo);
            }
        }

        //刷新报名信息
        List<PersonalSign> personalSigns =personSignService.querySignListByUserId(sheetUser.getUserId().intValue());

        boolean bhave =false;
        if (CollectionUtils.isEmpty(personalSigns)){
             bhave =false;
        }
        else {
            for (PersonalSign personalSign: personalSigns){
                if (personalSign.getPerSignPlanId().equals(planConfig.getPlanCfgId().intValue())){
                    bhave =true;
                    break;
                }
            }
        }

        if (!bhave){
            PersonalSign personalSign = new PersonalSign();
            personalSign.setPerSignUserId(sheetUser.getUserId().intValue());
            personalSign.setPerSignUpdateTime(new Date());
            personalSign.setPerSignPlanId(planConfig.getPlanCfgId());
            personalSign.setResult("1");
            personSignService.addSignInfo(personalSign);
        }
        else {
            PoolMgrInfo mgrInfo = new PoolMgrInfo();
            mgrInfo.setPoolUpdateTime(new Date());
            mgrInfo.setPoolUserId(sheetUser.getUserId().intValue());
            mgrInfo.setPoolPlanId(planConfig.getPlanCfgId());
            mgrInfo.setPoolScoreStatus(0);
            poolManualService.modifyPoolInfoByUserPlan(mgrInfo);
        }
    }

    /**
     * 读取该键对应的列信息
     */
    private String getValueByKey(String key,Map<Integer, String> cells, Map<String, Integer> col_pos) {
        Integer pos = col_pos.get(key);
        if (pos == null){
            return null ;
        }

        String value = cells.get(pos);

        if(value == null ){
            return null;
        }

        return  value;
    }

    /**
     * 读取该键对应的列信息
     */
    private String getValueByPos(Integer pos,Map<Integer, String> cells) {

        String value = cells.get(pos);

        if(value == null ){
            return null;
        }

        return  value;
    }
}
